package com.myres.servlet;
import com.myres.db.ConnectionPool;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;

import java.util.List;
import java.util.ArrayList;
import com.myres.model.Order;
import com.myres.model.OrderItem;
import java.sql.Timestamp;

public class MessageService {
	public int[] getNewMessageCount(int uid,int fromId){
		Connection con=null;
		try{
		con=ConnectionPool.getPool().getConnection();
		PreparedStatement pstmt=con.prepareStatement("select  (select max(_id) from res_messages where _receiver=? and _status=0 and _id>?)," +
				" (select count(_id) from res_messages where _receiver=? and _status=0)");
		pstmt.setInt(1, uid);
		pstmt.setInt(2, fromId);
		pstmt.setInt(3, uid);
		ResultSet rs=pstmt.executeQuery();
		if(!rs.next()) {
			int[] re=new int[2];
			re[0]=fromId;
			re[1]=0;
			return re;
		}else{
			int[] re=new int[2];
			re[0]=rs.getInt(1);
			re[1]=rs.getInt(2);
			return re;
		}
		}catch(SQLException e){
			e.printStackTrace();
			int[] re=new int[2];
			re[0]=fromId;
			re[1]=0;
			return re;
		}finally{
			try{
			con.close();
			}catch(Exception e){
				e.printStackTrace();
			}
			
		}
	}
	public int getNewOrderMaxId(int id){
		Connection con=null;
		try{
			con=ConnectionPool.getPool().getConnection();
			PreparedStatement pstmt=con.prepareStatement("select max(_id) from res_orders where _state=0 and _id>?");
			pstmt.setInt(1, id);
			ResultSet rs=pstmt.executeQuery();
			if(rs==null) return id;
			rs.next();
			return rs.getInt(1);
		}catch(Exception e){
			return id;
		}finally{
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
	}
	public List<Order> getNewOrder(){
		Connection con=null;
		try{
			con=ConnectionPool.getPool().getConnection();
			PreparedStatement pstmt=con.prepareStatement("" +
					"select res_orders._id," +
					"res_orders._payment_mode,"+
					"res_orders._payment," +
					"res_orders._send_to_place," +
					"res_orders._send_to_who," +
					"res_orders._receiver_phone," +
					"res_orders._deadline," +
					"res_orders._order_time," +
					"(select _name from res_products where res_products._id=res_order_items._product_id) as pName, " +
					"res_order_items._amount," +
					"res_order_items._price " +
					"from res_orders,res_order_items " +
					"where res_orders._id=res_order_items._order_id and res_orders._state=0");
			ResultSet rs=pstmt.executeQuery();
			int oid=0;
			Order order=new Order();
			List<Order> orders=new ArrayList<Order>();
			
			
			while(rs.next()){
				int one_oid=rs.getInt(1);
				if(one_oid!=oid){
					if(!(order.getId()==0)) orders.add(order);
					order=new Order();
				}
				int payment_mode=rs.getInt(2);
				float payment=rs.getFloat(3);
				String sendtoplace=rs.getString(4);
				String sendtowho=rs.getString(5);
				String receiverphone=rs.getString(6);
				Timestamp deadline=rs.getTimestamp(7);
				Timestamp ordertime=rs.getTimestamp(8);
				String pName=rs.getString(9);
				int amount=rs.getInt(10);
				float price=rs.getFloat(11);
				order.setId(one_oid);
				order.setPayment(payment);
				order.setPaymentMode(payment_mode);
				order.setSendToPlace(sendtoplace);
				order.setSendToWho(sendtowho);
				order.setReceiverPhone(receiverphone);
				order.setDeadline(deadline);
				order.setOrderTime(ordertime);
				
				OrderItem item=new OrderItem();
				item.setOid(one_oid);
				item.setName(pName);
				item.setAmount(amount);
				item.setPrice(price);
				order.getItems().add(item);
			}
			orders.add(order);
			return orders;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
	}

}
